# python 读取clickhouse方式
# 按照依赖  pip install clickhouse_driver
from clickhouse_driver import client
import mon_util

# 钉钉机器人地址
dd_url = 'https://oapi.dingtalk.com/robot/send?access_token=7aed8df41413753f81811932131a4d1ca20a122a68a176fe72738b14f1d2c7c8'

# 飞书机器人地址
fs_url = "https://open.feishu.cn/open-apis/bot/v2/hook/6ce90ca2-47b3-4e80-98e4-e830d98bb0d9"

if __name__ == '__main__':
    # 创建连接
    con = client.Client(host="master", port=9001, database="default")

    # 执行sql查询数据，返回一个列表
    records = con.execute("""
            select city_name,
               activity_name,
               formatDateTime(gmt_create, '%Y-%m-%d')                                       as ds,
               sum(case when routeEventStatus in (4, 5, 6, 7, ) then inquiry_money else 0 end) -
               sum(case when routeEventStatus in (4, 5, 6, 7, ) then real_money else 0 end) as quan_money
        from dws_record_activity_prize
        group by city_name, activity_name, formatDateTime(gmt_create, '%Y-%m-%d')
        having quan_money > 700
    """)

    # 循环发送告警信息
    for (city_name, activity_name, ds, quan_money) in records:
        msg = F"""
        告警信息：优惠券发放过多
        发放城市: {city_name}
        优惠券名：{activity_name}
        发放日期：{ds}
        发送金额：{quan_money}
        """

        # 发告警信息到钉钉和飞书
        mon_util.dd_msg(dd_url, msg)
        mon_util.fs_msg(fs_url, msg)
